package com.summit.exam.dbdao;


import com.summit.exam.MysqlConnection;
import com.summit.exam.entity.QuestionBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class QuestionDao {

    /********************************************
     *
     * @param: title
     * @param: paperId
     * @param: optionA
     * @param: optionB
     * @param: optionC
     * @param: optionD
     * @param: answer
     * 添加试题
     ********************************************/
    public boolean insertQuestion(String title, int paperId, String optionA, String optionB,
                                  String optionC, String optionD, String answer) {

        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        int rs = 0;

        try {
            stm = conn.prepareStatement("INSERT INTO question(title, paper_id, option_a, option_b," +
                    " option_c, option_d, answer) VALUES(?, ?, ?, ?, ?, ?, ?) ");

            stm.setString(1, title);
            stm.setInt(2, paperId);
            stm.setString(3, optionA);
            stm.setString(4, optionB);
            stm.setString(5,optionC);
            stm.setString(6, optionD);
            stm.setString(7, answer);

            rs = stm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }

    /**
     * 插入一千条
     *
     *
     */

    public boolean insertmoreQuestion(ArrayList<QuestionBean> arrayList, int paperId) {
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        int rs = 0;

        try {
            stm = conn.prepareStatement("INSERT INTO question(question_id, item_category, question_type," +
                    " title, paper_id, option_a, option_b, option_c, option_d, answer) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");


            for (int i = 0; i < arrayList.size(); i++) {
                QuestionBean question = arrayList.get(i);
                stm.setInt(1, question.getQuestionId());
                stm.setString(2, question.getItemCategory());
                stm.setString(3, question.getQuestionType());
                stm.setString(4, question.getTitle());
                stm.setInt(5, paperId);
                stm.setString(6, question.getOptionA());
                stm.setString(7, question.getOptionB());
                stm.setString(8,question.getOptionC());
                stm.setString(9, question.getOptionD());
                stm.setString(10, question.getAnswer());
                stm.addBatch();
            }
            stm.executeBatch();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }





    /********************************************
     *
     * @param: title
     * @param: paperId
     * @param: optionA
     * @param: optionB
     * @param: optionC
     * @param: optionD
     * @param: answer
     * 修改试题 paperId作为条件
     ********************************************/
    public boolean updateQuestion(String title, int questionId, String optionA, String optionB,
                                  String optionC, String optionD, String answer) {

        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        int rs = 0;

        try {
            stm = conn.prepareStatement("UPDATE question SET title = ?, option_a = ?, option_b = ?," +
                    "option_c = ?, option_d = ?, answer = ? WHERE question_id = ?");

            stm.setString(1, title);
            stm.setString(2, optionA);
            stm.setString(3, optionB);
            stm.setString(4, optionC);
            stm.setString(5, optionD);
            stm.setString(6, answer);
            stm.setInt(7, questionId);

            rs = stm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }

    /********************************************
     *
     * @param: title
     * 删除试题 根据title删除
     ********************************************/
    public boolean deleteQuestion(int qid) {
        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        int rs = 0;

        try {
            stm = conn.prepareStatement("DELETE FROM question WHERE question_id = ?");

            stm.setInt(1, qid);
            rs = stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return (rs != 0);
    }

    /********************************************
     *
     * @param: paperId
     * 查询试题 根据paperId查询一套试题
     ********************************************/
    public ArrayList<QuestionBean> selectQuestion(int paperId) {

        ArrayList<QuestionBean> questionArrayList = new ArrayList<QuestionBean>();

        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        try {
            stm = conn.prepareStatement("SELECT * FROM question WHERE paper_id = ?");

            stm.setInt(1, paperId);
            resultSet = stm.executeQuery();

            while (resultSet.next()) {
                QuestionBean question = new QuestionBean();
                question.setQuestionId(resultSet.getInt("question_id"));
                question.setItemCategory(resultSet.getString("item_category"));
                question.setQuestionType(resultSet.getString("question_type"));
                question.setTitle(resultSet.getString("title"));
                question.setPaperId(resultSet.getInt("paper_id"));
                question.setOptionA(resultSet.getString("option_a"));
                question.setOptionB(resultSet.getString("option_b"));
                question.setOptionC(resultSet.getString("option_c"));
                question.setOptionD(resultSet.getString("option_d"));
                question.setAnswer(resultSet.getString("answer"));

                questionArrayList.add(question);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return questionArrayList;
    }

    /********************************************
     *
     * @param: paperId
     * 查询试题 根据paperId查询一道试题
     ********************************************/
    public QuestionBean selectQuestionone(int questionId) {
        QuestionBean question = new QuestionBean();

        MysqlConnection mysqlConnection = MysqlConnection.getMysqlConnection();
        Connection conn = mysqlConnection.getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        try {
            stm = conn.prepareStatement("SELECT * FROM question WHERE question_id  = ?");

            stm.setInt(1, questionId);

            resultSet = stm.executeQuery();

            while (resultSet.next()) {
                question.setQuestionId(resultSet.getInt("question_id"));
                question.setItemCategory(resultSet.getString("item_category"));
                question.setQuestionType(resultSet.getString("question_type"));
                question.setTitle(resultSet.getString("title"));
                question.setPaperId(resultSet.getInt("paper_id"));
                question.setOptionA(resultSet.getString("option_a"));
                question.setOptionB(resultSet.getString("option_b"));
                question.setOptionC(resultSet.getString("option_c"));
                question.setOptionD(resultSet.getString("option_d"));
                question.setAnswer(resultSet.getString("answer"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return question;
    }

    /********************************************
     *
     * @param: 表名
     * 获取分页信息
     ********************************************/
    public ArrayList<QuestionBean> findPageBean(int start, int currentCount) {
        ArrayList<QuestionBean> arrayList = new ArrayList();
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        try {
            stm = conn.prepareStatement("SELECT * FROM question limit ?, ?");

            stm.setInt(1, start);
            stm.setInt(2, currentCount);

            resultSet = stm.executeQuery();

            while (resultSet.next()) {
                QuestionBean question = new QuestionBean();

                question.setQuestionId(resultSet.getInt("question_id"));
                question.setItemCategory(resultSet.getString("item_category"));
                question.setQuestionType(resultSet.getString("question_type"));
                question.setTitle(resultSet.getString("title"));
                question.setPaperId(resultSet.getInt("paper_id"));
                question.setOptionA(resultSet.getString("option_a"));
                question.setOptionB(resultSet.getString("option_b"));
                question.setOptionC(resultSet.getString("option_c"));
                question.setOptionD(resultSet.getString("option_d"));
                question.setAnswer(resultSet.getString("answer"));

                arrayList.add(question);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        return arrayList;
    }

    /********************************************
     *
     * @param: 表名
     * 查询总页数
     ********************************************/
    public int getTotalCount() {
        Connection conn = MysqlConnection.getMysqlConnection().getCon();
        PreparedStatement stm = null;
        ResultSet resultSet = null;

        try {
            stm = conn.prepareStatement("SELECT COUNT(*) FROM question");

            resultSet = stm.executeQuery();

            while (resultSet.next()) return resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                MysqlConnection.CloseConn(resultSet, stm, conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }
}
